﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dll.Model;
using System.Data.SQLite;

namespace Dll
{
    public class ShufatiyanService
    {
        public ShufatiyanService(String dbPath) {
            this.sqlHelp = new SqlHelp(dbPath);
        }
        SqlHelp sqlHelp;
        public List<Category> FindCategoryList()
        {
            List<Category> list = new List<Category>();
            using (SQLiteDataReader dr = sqlHelp.ExecuteReader("SELECT * FROM category"))
            {
                while (dr.Read())
                {
                    Type t = dr[3].GetType();
                    Category c = new Category();
                    c.id = dr.GetString(0);
                    c.title = dr.GetString(1);
                    c.amont = dr.GetInt32(2);
                    c.introduction = Convert.IsDBNull(dr[3]) ? "" : dr.GetString(3);
                    c.parenId = Convert.IsDBNull(dr[4]) ? "" : dr.GetString(4);
                    c.sort = dr.GetInt32(5);
                    c.customize = Convert.IsDBNull(dr[6]) ? "" : dr.GetString(6);
                    list.Add(c);
                }
            }
            return list;
        }
        public List<MetaInfo> FindMetaInfoListByCategoryId(String categoryId, int pageIndex, int pageSize)
        {
            List<MetaInfo> list = new List<MetaInfo>();
            using (SQLiteDataReader dr = sqlHelp.ExecuteReader("SELECT * FROM metaInfo WHERE categoryId =@categoryId LIMIT @pageSize OFFSET @pageIndex", new SQLiteParameter("@categoryId", categoryId), new SQLiteParameter("@pageSize", pageSize), new SQLiteParameter("@pageIndex", pageIndex * pageSize)))
            {
                while (dr.Read())
                {

                    MetaInfo m = new MetaInfo();
                    m.id = Convert.IsDBNull(dr[0]) ? "" : dr.GetString(0);
                    m.categoryId = Convert.IsDBNull(dr[1]) ? "" : dr.GetString(1);
                    m.title = Convert.IsDBNull(dr[2]) ? "" : dr.GetString(2);
                    m.identify = Convert.IsDBNull(dr[3]) ? "" : dr.GetString(3);
                    m.introduction = Convert.IsDBNull(dr[4]) ? "" : dr.GetString(4);
                    m.col = dr.GetInt32(5);
                    m.row = dr.GetInt32(6);
                    m.blockSize = dr.GetInt32(7);
                    m.sort = dr.GetInt32(8);
                    m.type = Convert.IsDBNull(dr[9]) ? "" : dr.GetString(9);
                    m.customize = Convert.IsDBNull(dr[10]) ? "" : dr.GetString(10);
                    list.Add(m);
                }
            }
            return list;
        }
        public MetaInfo FindMetaInfoById(String id) {
            MetaInfo m=null;
            using (SQLiteDataReader dr = sqlHelp.ExecuteReader("SELECT * FROM metaInfo WHERE id = @id", new SQLiteParameter("@id", id)))
            {
                if (dr.Read())
                {
                    m = new MetaInfo();
                    m.id = Convert.IsDBNull(dr[0]) ? "" : dr.GetString(0);
                    m.categoryId = Convert.IsDBNull(dr[1]) ? "" : dr.GetString(1);
                    m.title = Convert.IsDBNull(dr[2]) ? "" : dr.GetString(2);
                    m.identify = Convert.IsDBNull(dr[3]) ? "" : dr.GetString(3);
                    m.introduction = Convert.IsDBNull(dr[4]) ? "" : dr.GetString(4);
                    m.col = dr.GetInt32(5);
                    m.row = dr.GetInt32(6);
                    m.blockSize = dr.GetInt32(7);
                    m.sort = dr.GetInt32(8);
                    m.type = Convert.IsDBNull(dr[9]) ? "" : dr.GetString(9);
                    m.customize = Convert.IsDBNull(dr[10]) ? "" : dr.GetString(10);
                }
            }
            return m;
        }
        public List<File> FindFileListByMetaInfoId(String metaInfoId) {
            List<File> list = new List<File>();
            using (SQLiteDataReader dr = sqlHelp.ExecuteReader("SELECT * FROM file WHERE metaInfoId = @metaInfoId", new SQLiteParameter("@metaInfoId", metaInfoId)))
            {
                while (dr.Read())
                {
                   File m = new File();
                    m.id = Convert.IsDBNull(dr[0]) ? "" : dr.GetString(0);
                    m.fileName = Convert.IsDBNull(dr[1]) ? "" : dr.GetString(1);
                    m.metaInfoId = Convert.IsDBNull(dr[2]) ? "" : dr.GetString(2);
                    m.number = dr.GetInt32(3);
                    m.customize = Convert.IsDBNull(dr[4]) ? "" : dr.GetString(4);
                    list.Add(m);
                }
            }
            return list;
        }
        public File FindPreviewFileByMetaInfoId(String metaInfoId)
        {
            File m = null;
            using (SQLiteDataReader dr = sqlHelp.ExecuteReader("SELECT * FROM file WHERE metaInfoId = @metaInfoId AND number = 0", new SQLiteParameter("@metaInfoId", metaInfoId)))
            {
                if (dr.Read())
                {
                    m = new File();

                    m.id = Convert.IsDBNull(dr[0]) ? "" : dr.GetString(0);
                    m.fileName = Convert.IsDBNull(dr[1]) ? "" : dr.GetString(1);
                    m.metaInfoId = Convert.IsDBNull(dr[2]) ? "" : dr.GetString(2);
                    m.number = dr.GetInt32(3);
                    m.customize = Convert.IsDBNull(dr[4]) ? "" : dr.GetString(4);
                }
            }
            return m;
        }
    }
}
